IF OBJECT_ID('dbo.csp_rptDepartmentalMetric_SummaryThisPeriod') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.csp_rptDepartmentalMetric_SummaryThisPeriod
END
GO

-- =======================================================================
-- Procedure Name: dbo.csp_rptDepartmentalMetric_SummaryThisPeriod
--
-- Author:      Arthur Bianchini  Partners Research Enterprise Group
--
-- Create date: 8/25/2008
--
-- Description: Gets summary data for the current week for the Departmental Metrics Report
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 08/25/09	AAB		TRACKIT 9858
-- =======================================================================

CREATE PROCEDURE dbo.csp_rptDepartmentalMetric_SummaryThisPeriod
(
@PeriodType varchar(50),
@Institution INT,      
@PeriodEndDate SMALLDATETIME,  
@Department INT = NULL,    
@Unit VARCHAR(250)  = NULL,
@PrincipalInvestigator VARCHAR(50)=NULL,    
@Sponsor  VARCHAR(150) = NULL,    
@FundNumber VARCHAR(50) = NULL, -- no information yet   
@DomainUserId INT =  NULL
)

AS
BEGIN
 
 -- Result Set
CREATE TABLE   #Proposal 
						(agreementID int,
                         Foldernumber varchar(12))

CREATE CLUSTERED INDEX IX_#Proposal_agreementID
    ON #Proposal (AgreementID); 

     INSERT INTO #Proposal
     
     SELECT sec.agreementID,sec.Foldernumber
     FROM INSIGHT_RPT_DB.dbo.fnGetSecurityAgreements(@DomainUserId) sec 

SELECT
	ai.Institution
,	x.PeriodType
,	x.PeriodStartDate
,	PendingSubmissionDetails = SUM(CASE WHEN Subject='Pending Submission Details' THEN 1 ELSE 0 END)
,	AwardsActivated = SUM(CASE WHEN Subject='Awards Activated' THEN 1 ELSE 0 END)
,	AwardsToBeActivated = SUM(CASE WHEN Subject='Awards To Be Activated' THEN 1 ELSE 0 END)
,	PCROAwardsToBeActivated = SUM(CASE WHEN Subject='PCRO Awards To Be Activated' THEN 1 ELSE 0 END)
,	PreAwardJITWeek = SUM(CASE WHEN Subject='Pre-Award JIT Week' THEN 1 ELSE 0 END)
,	AdvanceAccountWeek = SUM(CASE WHEN Subject='Advance Account Week' THEN 1 ELSE 0 END)
,	RenewalsProcessed = SUM(CASE WHEN Subject='Renewals Processed' THEN 1 ELSE 0 END)
,	AccountInactivated = SUM(CASE WHEN Subject='Accounts Inactivated' THEN 1 ELSE 0 END)
FROM
(
	SELECT
		Institution
	,	ProposalNumber
	,	PeriodType
	,	PeriodStartDate
	,	Subject = 'Accounts Inactivated'
	FROM
		[dbo].[rptMetric_AccountsInactivated]
	UNION ALL
	SELECT
		Institution
	,	ProposalNumber
	,	PeriodType
	,	PeriodStartDate
	,	Subject = 'Advance Account Week'
	FROM
		[dbo].[rptMetric_AdvanceAccountWeek]
	UNION ALL
	SELECT
		Institution
	,	ProposalNumber
	,	PeriodType
	,	PeriodStartDate
	,	Subject = 'Awards Activated'
	FROM
		[dbo].[rptMetric_AwardActivations]
	UNION ALL
	SELECT
		Institution
	,	ProposalNumber
	,	PeriodType
	,	PeriodStartDate
	,	Subject = 'Awards To Be Activated'
	FROM
		[dbo].[rptMetric_AwardsToBeActivated]
	UNION ALL
	SELECT
		Institution
	,	ProposalNumber
	,	PeriodType
	,	PeriodStartDate
	,	Subject = 'PCRO Awards To Be Activated'
	FROM
		[dbo].[rptMetric_PCROAwardsToBeActivated]
	UNION ALL
	SELECT
		Institution
	,	ProposalNumber
	,	PeriodType
	,	PeriodStartDate
	,	Subject = 'Pending Submission Details'
	FROM
		[dbo].[rptMetric_PendingSubmissions]
	UNION ALL
	SELECT
		Institution
	,	ProposalNumber
	,	PeriodType
	,	PeriodStartDate
	,	Subject = 'Pre-Award JIT Week'
	FROM
		[dbo].[rptMetric_PreAwardJITWeek]
	UNION ALL
	SELECT
		Institution
	,	ProposalNumber
	,	PeriodType
	,	PeriodStartDate
	,	Subject = 'Renewals Processed'
	FROM
		[dbo].[rptMetric_RenewalsProcessed]
	) AS x
INNER JOIN  INSIGHT_RPT_DB.dbo.AgrAgreement_Info ai
	ON	x.ProposalNumber = ai.foldernumber
INNER JOIN	#Proposal pr
	ON ai.agreementID = pr.agreementID 
WHERE
	1=1
	AND		(ai.InstitutionId = @Institution  OR @Institution IS NULL OR @Institution = 0)
			AND (ai.DepartmentId = @Department OR @Department IS NULL OR @Department=0 )
			AND (ai.UnitId in (SELECT * FROM dbo.fnSplitFundNumbers(@Unit)) OR @Unit IS NULL OR @Unit='0' OR @Unit='')
			AND (ai.PrincipalInvestigatorId = @PrincipalInvestigator OR @PrincipalInvestigator IS NULL OR @PrincipalInvestigator='' OR @PrincipalInvestigator='0')    
			AND (ai.Sponsorid = @Sponsor OR @Sponsor IS NULL OR @Sponsor='' OR @Sponsor='0')
			AND (ai.ProjectFundNumber IN (SELECT * FROM dbo.fnSplitFundNumbers(@FundNumber)) OR @FundNumber IS NULL OR @FundNumber='')
			AND	x.PeriodStartDate = @PeriodEndDate  
			AND x.PeriodType=@PeriodType
GROUP BY
	ai.Institution
,	x.PeriodType
,	x.PeriodStartDate


END

GO



